from flask import Blueprint, render_template, request, redirect, url_for, flash, make_response, session
from function.sqlite_function import *
import os
_make = Blueprint('make', __name__, url_prefix='/')


# 基本页面
@_make.route('/make/index')
def make_index():

    con, cur = db_open()
    bigname = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    # 小类搜索框中的预设文字
    temp1 = [['请输入', '']]
    if session.get('identity') == '用户':
        return render_template('make/make.html', bigname=bigname, temp1=temp1)
    else:
        return render_template('make/make_visitor.html', bigname=bigname, temp1=temp1)


# 点击返回小类表
@_make.route('/make8', methods=['POST'])
def make8():
    bigname = request.form.get('a')
    con, cur = db_open()
    data = sqliteObject_to_list_h(cur, f'''
        select smallname, unitprice, number, round(total,0), photo from stateless_part where bigname = '{bigname}'
    ''')
    return render_template('make/smallname_table.html', data=data, bigname=bigname)


# 返回大类
@_make.route('/make9', methods=['POST'])
def make9():
    con, cur = db_open()
    bigname = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    return render_template('make/bigname_table.html', bigname=bigname)


# 创建大类和小类
@_make.route('/make1', methods=['POST'])
def make1():
    a = request.form.get('big_name').strip()
    b = request.form.get('small_name').strip()
    c = request.files.get('photo')
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    if a in temp1:
        # 要创建的大类已经存在
        return '1'
    temp2 = sqliteObject_to_list_h(cur, f'''
        select smallname from stateless_part where bigname = "{a}" 
    ''')
    if b in temp2:
        # 该大类中已经存在该小类
        return '2'
    # 开始存储
    if c:
        path = current_app.photo + '/' + a + '-' + b + '.jpg'
        photo = a + '-' + b + '.jpg'
        c.save(path)
    else:
        photo = 'null.jpg'
    sql1 = '''
        insert into stateless_part(
            bigname,
            smallname,
            photo,
            unitprice,
            number,
            total
            )
            values(
                ?,
                ?,
                ?,
                ?,
                ?,
                ?
        )
    '''
    sql2 = f'''
        create table {a}
        (
            date text,
            bigname text,
            smallname text,
            handle text,
            change numeric,
            unitprice numeric,
            price numeric
        );
    '''
    cur.execute(sql1, (a, b, photo, 0, 0, 0))
    cur.execute(sql2)
    con.commit()
    # 创建成功
    return '3'


# 创建小类
@_make.route('/make2', methods=['POST'])
def make2():
    a = request.form.get('big_name').strip()
    b = request.form.get('small_name').strip()
    c = request.files.get('photo')
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    if a not in temp1:
        # 要存入的大类不存在
        return '1'
    temp2 = sqliteObject_to_list_a(cur, f'''
        select smallname from stateless_part where bigname = '{a}'
    ''')
    if b in temp2:
        # 该小类已经在大类中存在
        return '2'

    # 开始创建
    # 图片存储
    if c:
        path = current_app.photo + '/' + a + '-' + b + '.jpg'
        photo = a + '-' + b + '.jpg'
        c.save(path)
    else:
        photo = 'null.jpg'
    sql = '''
        insert into stateless_part(
            bigname,
            smallname,
            photo,
            unitprice,
            number,
            total
            )
            values(
                ?,
                ?,
                ?,
                ?,
                ?,
                ?
        )
    '''
    cur.execute(sql, (a, b, photo, 0, 0, 0))
    con.commit()
    # 创建成功
    return '3'


# 重命名大类
@_make.route('/make3', methods=['POST'])
def make3():
    a = request.form.get('original_name').strip()
    b = request.form.get('now_name').strip()
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select distinct bigname from stateless_part
    ''')
    if a not in temp1:
        # 要更改名称的大类不存在
        return '1'
    if b in temp1:
        # 想要变更的新名称已经存在
        return '2'
        # 重命名步骤：修改目录中的名字，自改大类表名，修改大类表中的名字
    sql1 = f'''
        UPDATE stateless_part SET bigname = '{b}' WHERE bigname = '{a}'
    '''
    sql2 = f'''
        ALTER TABLE '{a}' RENAME TO '{b}'
    '''
    sql3 = f'''
        UPDATE '{b}' SET bigname = '{b}' WHERE bigname = '{a}'
    '''
    cur.execute(sql1)
    cur.execute(sql2)
    cur.execute(sql3)
    con.commit()  # 插入数据，修改表名时需要用
    # 创建成功
    return '3'


# 重命名小类
@_make.route('/make4', methods=['POST'])
def make4():
    a = request.form.get('big_name').strip()
    b = request.form.get('original_small_name').strip()
    c = request.form.get('new_small_name').strip()
    con, cur = db_open()

    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    if a not in temp1:
        # 大类不存在
        return '1'

    temp2 = sqliteObject_to_list_a(cur, f'''
        select smallname from stateless_part where bigname = '{a}'
    ''')
    if b not in temp2:
        # 小类不存在
        return '2'
    if c in temp2:
        # 要变更的小类名称已经存在
        return '3'
        # 重命名步骤：修改目录中的名字，自改大类表名，修改大类表中的名字
    sql1 = f'''
        UPDATE stateless_part SET smallname = '{c}' WHERE bigname = '{a}' and smallname = '{b}'
    '''
    sql2 = f'''
        UPDATE '{a}' SET smallname = '{c}' WHERE bigname = '{a}' and smallname = '{b}'
    '''
    cur.execute(sql1)
    cur.execute(sql2)
    con.commit()  # 插入数据，修改表名时需要用
    # 重命名小类成功
    return '4'


# 删除大类
@_make.route('/make5', methods=['POST'])
def make5():
    a = request.form.get('big_name').strip()
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    if a not in temp1:
        # 要删除的大类不存在
        return '1'
    # 开始删除
    # 先删除图片
    # 删除该大类下对应的全部图片
    temp2 = sqliteObject_to_list_a(cur, f'''
        select photo from stateless_part where bigname = '{a}'
    ''')
    for i in temp2:
        if i != 'null.jpg':
            path = current_app.photo + '/' + i
            os.remove(path)

    sql1 = f'''
        delete from stateless_part where bigname = '{a}'
    '''
    sql2 = f'''
        DROP TABLE {a}
    '''
    cur.execute(sql1)
    cur.execute(sql2)
    con.commit()  # 插入数据，修改表名时需要用
    # 删除成功
    return '2'


# 删除小类
@_make.route('/make6', methods=['POST'])
def make6():
    a = request.form.get('big_name').strip()
    b = request.form.get('small_name').strip()
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    if a not in temp1:
        # 要删除的小类所在的大类不存在
        return '1'
    temp2 = sqliteObject_to_list_a(cur, f'''
        select smallname from stateless_part where bigname = '{a}'
    ''')
    if b not in temp2:
        # 要删除小类不存在
        return '2'

    # 开始删除

    # 删除该小类对应的图片
    temp2 = sqliteObject_to_list_a(cur, f'''
        select photo from stateless_part where bigname = '{a}' and smallname = '{b}'
    ''')
    for i in temp2:
        if i != 'null.jpg':
            path = current_app.photo + '/' + i
            os.remove(path)

    sql1 = f'''
        delete from stateless_part where bigname = '{a}' and smallname = '{b}'
    '''
    sql2 = f'''
        delete from {a} where bigname = '{a}' and smallname = '{b}'
    '''
    cur.execute(sql1)
    cur.execute(sql2)
    con.commit()
    # 删除成功
    return '3'


# 更换图片
@_make.route('/make7', methods=['POST'])
def make7():
    a = request.form.get('big_name').strip()
    b = request.form.get('small_name').strip()
    c = request.files.get('photo')
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select bigname from stateless_part
    ''')
    if a not in temp1:
        # 要修改图片的小类所在的大类不存在
        return '1'
    temp2 = sqliteObject_to_list_h(cur, f'''
        select smallname from stateless_part where bigname = "{a}" 
    ''')
    if b in temp2:
        # 要修改图片的小类不存在
        return '2'
    # 开始存储
    # print(c.mimetype)
    # 存入数据库
    name = a + '-' + b + '.jpg'
    sql = f'''
        UPDATE stateless_part SET photo = '{name}' where bigname = '{a}' and smallname = '{b}'
    '''
    cur.execute(sql)
    con.commit()
    # 判断用户是否真的上传了图片
    if not c:
        # 您没有上传图片
        return '3'
    else:
        path = current_app.photo + '/' + a + '-' + b + '.jpg'
        # 如果有旧照片的话就删除
        if os.path.exists(path):
            os.remove(path)
        c.save(path)
        # 图片更换成功
        return '4'


